USE NutritionTracker
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[fk_AppUser_MeasurementUnit_MeasurementUnit]') AND parent_object_id = OBJECT_ID(N'[AppUser_MeasurementUnit]'))
	ALTER TABLE [AppUser_MeasurementUnit] DROP CONSTRAINT [fk_AppUser_MeasurementUnit_MeasurementUnit]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MeasurementUnit') AND type in (N'U'))
	DROP TABLE MeasurementUnit
GO
CREATE TABLE	MeasurementUnit
(
	MeasurementUnitID	TINYINT IDENTITY(1,1) NOT NULL,
	ParentMeasurementUnitID	TINYINT NULL,
	MeasurementTypeID	TINYINT	NOT NULL,
	MeasurementUnitName		NVARCHAR(10) NOT NULL,	
	MeasurementUnitFullName		NVARCHAR(50) NOT NULL,
	UnitFactor	FLOAT NOT NULL,
	CONSTRAINT pk_MeasurementUnit PRIMARY KEY(MeasurementUnitID ASC)
)
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'fk_MeasurementUnit_MeasurementType') AND parent_object_id = OBJECT_ID(N'MeasurementUnit'))
	ALTER TABLE MeasurementUnit  WITH CHECK ADD  CONSTRAINT fk_MeasurementUnit_MeasurementType FOREIGN KEY(MeasurementTypeID) REFERENCES MeasurementType (MeasurementTypeID)
GO

ALTER TABLE [AppUser_MeasurementUnit]  WITH CHECK ADD  CONSTRAINT [fk_AppUser_MeasurementUnit_MeasurementUnit] FOREIGN KEY([MeasurementUnitID]) REFERENCES [MeasurementUnit] ([MeasurementUnitID])
GO